package com.github.common.util.io;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库JDBC连接工具类
 */
public class JDBCUtil {

    /**
     * 通过sql查询数据
     *
     * @param conn conn
     * @param sql sql
     * @return 查询的数据集合
     * @throws SQLException SQLException
     */
    public static List<Map<String, Object>> query(Connection conn, String sql) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            return getData(rs);
        } finally {
            StreamUtil.close(rs, stmt, conn);
        }
    }

    /**
     * 通过sql查询数据
     *
     * @param conn conn
     * @param sql sql
     * @return 查询的数据集合
     * @throws SQLException SQLException
     */
    public static List<Map<String, Object>> query(Connection conn, String sql, boolean closeConn) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            return getData(rs);
        } finally {
            StreamUtil.close(rs, stmt);
            if (closeConn){
                StreamUtil.close(conn);
            }
        }
    }

    /**
     * 通过sql查询数据
     *
     * @param conn conn
     * @param sql sql
     * @param column column
     * @return 查询的数据集合
     * @throws SQLException SQLException
     */
    public static String queryOnlyString(Connection conn, String sql, String column) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString(column);
            }
            return null;
        } finally {
            StreamUtil.close(rs, stmt, conn);
        }
    }

    /**
     * 通过sql查询数据
     *
     * @param conn conn
     * @param sql sql
     * @param column column
     * @return 查询的数据集合
     * @throws SQLException SQLException
     */
    public static Integer queryOnlyInt(Connection conn, String sql, String column) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(column);
            }
            return null;
        } finally {
            StreamUtil.close(rs, stmt, conn);
        }
    }

    /**
     * 将结果集对象封装成List<Map<String, Object>> 对象
     *
     * @param rs 结果多想
     * @return 结果的封装
     * @throws SQLException SQLException
     */
    private static List<Map<String, Object>> getData(ResultSet rs) throws SQLException {
        //获取结果集的数据结构对象
        ResultSetMetaData metaData = rs.getMetaData();
        List<Map<String, Object>> data = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                rowMap.put(metaData.getColumnName(i), rs.getObject(i));
            }
            data.add(rowMap);
        }
        return data;
    }

    public static void execute(Connection conn, String sql, boolean closeConn) throws SQLException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute(sql);
        } finally {
            StreamUtil.close(stmt);
            if (closeConn) {
                StreamUtil.close(conn);
            }
        }
    }

}
